package com.fare.taluo.queue;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.Serializable;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

import javax.annotation.Resource;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.Border;
import jxl.write.Label;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

import org.apache.commons.lang3.StringUtils;

import com.dayatang.domain.Entity;
import com.dayatang.domain.InstanceFactory;
import com.fare.taluo.application.report.csair.CsairReportApplication;
import com.fare.taluo.application.system.email.EmailSendApplication;
import com.fare.taluo.application.system.email.ReportEmailApplication;
import com.fare.taluo.core.domain.BaseEntity;
import com.fare.taluo.core.domain.queue.message.QueueEmailMessageEntity;
import com.fare.taluo.util.DateUtils;
import com.fare.taluo.vo.report.CsairReportVO;
import com.fare.taluo.vo.reportemail.EmailVO;
import com.fare.util.ReportProperties;

public class ExportCsairReportInlandQuartzJob extends
		BaseEntity<ExportCsairReportInlandQuartzJob> implements Entity {
	/**
	 * 
	 */
	private static final long serialVersionUID = 466060663771737758L;
	private static final int COUNT = 10;// 生成当天及未来9天一共10个sheet
	private String email = null;
	private String currentDate = DateUtils.getDateStr("yyyy-MM-dd");
	private List<String> dateList = getDateList();
	private Map<String, List<CsairReportVO>> reportMap;
	@Resource(name = "csairReportApplication")
	private CsairReportApplication csairReportApplication;
	private jxl.write.WritableFont titleFont;
	private jxl.write.WritableCellFormat titleFormat;
	private jxl.write.WritableFont normalFont;
	private jxl.write.WritableFont boldFont;
	private jxl.write.WritableCellFormat normalFormat;
	private jxl.write.WritableCellFormat rangeFormat;
	private jxl.write.WritableCellFormat boldFormat;
	private jxl.write.WritableCellFormat redFormat;
	private static final String WEB_TYPE = "1";
	private static final int COLUMN_WIDTH = 13;
	@Resource(name ="reportEmailApplication")
	private ReportEmailApplication reportEmailApplication;
	
	private List<EmailVO> emailList;
	public void execute() throws Exception {
		try {
			/*
			 * this.initDate(); sheet1List = ReportStaticData.initSheet1();
			 * sheet2List = ReportStaticData.initSheet2();
			 */

			// 获得数据
			reportMap = this.getReportMap();
			/*
			 * sheet1Map = this.getSheet1Map(); sheet2Map = this.getSheet2Map();
			 */
			// 生成路径
			String path = ReportProperties.getInstance().getSavePath()
					.endsWith("/") ? ReportProperties.getInstance()
					.getSavePath() : ReportProperties.getInstance()
					.getSavePath() + "/";

			String fileName = "南航国内渠道监控报表_" + currentDate + ".xls";
			// 生成excel
			exportExcel(reportMap, path, fileName, dateList);
			/*
			 * exportExcel(sheet1Map, sheet2Map,path,fileName);
			 */
			File file = new File(path + fileName);
			
			//获取后台要发送的Email
			if(email==null || email==""){
				emailList=reportEmailApplication.findAllEmail(1);//0表示不接受状态，1表示接受状态
				StringBuffer emailStr=new StringBuffer();
				for(EmailVO emailVo:emailList){
					emailStr.append(","+emailVo.getEmailAddress());
				}
				if(emailStr.length()>1 && emailStr !=null){
					email=emailStr.toString().substring(1);
				}
			}
			if (file.exists() && StringUtils.isNotBlank(getEmail())) {
				QueueEmailMessageEntity email = new QueueEmailMessageEntity();
				email.setAttachPath(file.getPath());// new
													// String(location.getBytes("UTF-8"),"ISO8859-1")
				email.setBody("<p>您好!<p><p>&nbsp;&nbsp;已生成您需要的国内渠道监控报表，"
						+ "</p>" + "<p>请查收附件。</p>");
				email.setSubject("国内渠道监控报表" + currentDate);
				email.setToAddress(getEmail());
				email.setName("国内渠道监控报表");
				InstanceFactory.getInstance(EmailSendApplication.class).save(
						email);

			}
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		}

	}

	/**
	 * 获得10天的航班信息
	 * 
	 * @return
	 */
	private Map<String, List<CsairReportVO>> getReportMap() {
		Map<String, List<CsairReportVO>> map = new HashMap<String, List<CsairReportVO>>();
		for (int i = 0; i < dateList.size(); i++) {
			// 根据日期获得该日期下得航班信息
			List<CsairReportVO> infoList = csairReportApplication
					.findCsairEntityByCondition(null, null, null, null, null,
							dateList.get(i), null,null, WEB_TYPE);
			map.put(dateList.get(i), infoList);
		}
		return map;
	}

	private void exportExcel(Map<String, List<CsairReportVO>> reportMap,
			String path, String fileName, List<String> dateList)
			throws Exception {
		WritableWorkbook wwb = null;
		OutputStream os = null;
		File file = null;
		try {
			// this.initDate();
			file = this.createFile(path, fileName);
			os = new FileOutputStream(file);
			wwb = Workbook.createWorkbook(os); // 建立excel文件
			// 定义样式
			jxl.write.WritableFont normalFont = new jxl.write.WritableFont(
					WritableFont.createFont("宋体"), 10);
			jxl.write.WritableCellFormat normalFormat = new jxl.write.WritableCellFormat(
					normalFont);
			normalFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
			normalFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
			normalFormat.setAlignment(Alignment.CENTRE);
			normalFormat.setWrap(false);
			for (int i = 0; i < dateList.size(); i++) {
				WritableSheet ws = wwb.createSheet(dateList.get(i), i); // sheet名称
				ws = setHeadFirst(ws);
				ws = setDataFirst(ws, reportMap.get(dateList.get(i)));
			}
			wwb.write();
		} finally {

			if (wwb != null)
				wwb.close();
			if (os != null)
				os.close();
			wwb = null;
			os = null;
			file = null;
		}
	}

	private WritableSheet setDataFirst(WritableSheet ws,
			List<CsairReportVO> infoList) throws Exception {
		this.formatWriteable();
		Map<String, List<CsairReportVO>> map = new HashMap<String, List<CsairReportVO>>();
		for (CsairReportVO csairReportVO : infoList) {
			String key = csairReportVO.getFromCity() + "-"
					+ csairReportVO.getToCity();
			if (map.containsKey(key)) {
				map.get(key).add(csairReportVO);
			} else {
				List<CsairReportVO> temp = new ArrayList<CsairReportVO>();
				temp.add(csairReportVO);
				map.put(key, temp);
			}
		}
		TreeMap<String, List<CsairReportVO>> treeMap = new TreeMap<String, List<CsairReportVO>>(
				map);

		Iterator lineKeys = treeMap.keySet().iterator();
		int i=1;
		int col = 1;
		while (lineKeys.hasNext()) {
			String key = (String) lineKeys.next();// 航线
			List<CsairReportVO> listTmp = map.get(key);
			// 对每个对象循环
			for (int k=0; k < listTmp.size();k++) {
				CsairReportVO vo = listTmp.get(k);
				Double isTaobao = vo.getTaobaoPric();
				// 序号
				Label label0 = new Label(0, i, i + "", normalFormat);
				ws.addCell(label0);
				// 航线
				Label label1 = new Label(1, i, vo.getFromCity() + "-"
						+ vo.getToCity(), normalFormat);
				ws.addCell(label1);
				// 日期
				Label label2 = new Label(2, i, "" + vo.getFlightTime(),
						normalFormat);
				ws.addCell(label2);
				// 航班
				Label label3 = new Label(3, i, vo.getFlightNo(), normalFormat);
				ws.addCell(label3);
				// 航程类型
				Label label4 = new Label(4, i, vo.getFlightType() == 0 ? "单程"
						: "往返", normalFormat);
				ws.addCell(label4);

				if (null != isTaobao && null != vo.getTaobaoPric()) {
					Label label5 = new Label(5, i, "淘宝旅行网", normalFormat);
					ws.addCell(label5);
					Label label6 = new Label(6, i,
							vo.getTaobaoAgentName() == null ? ""
									: vo.getTaobaoAgentName(), normalFormat);
					ws.addCell(label6);
					Label label7 = new Label(7, i, vo.getPrice() == null ? ""
							: vo.getPrice() + "", normalFormat);
					ws.addCell(label7);
					Label label8 = new Label(8, i,
							vo.getTaobaoPric() == null ? "" : vo
									.getTaobaoPric() + "", normalFormat);
					ws.addCell(label8);
					Label label9 = new Label(9, i,
							vo.getTaobaoSpreadAmount() == null ? "" : vo
									.getTaobaoSpreadAmount() + "", normalFormat);
					ws.addCell(label9);
					Label label10 = new Label(10, i,
							vo.getTaobaoSpread() == null ? "" :  String.format("%.2f",vo
											.getTaobaoSpread())+"%", normalFormat);
					ws.addCell(label10);
				} else if(null != vo.getQunaerPrice()){
					Label label5 = new Label(5, i, "去哪儿网", normalFormat);
					ws.addCell(label5);
					Label label6 = new Label(6, i,
							vo.getQunaerAgentName() == null ? "" : vo
									.getQunaerAgentName() + "", normalFormat);
					ws.addCell(label6);
					Label label7 = new Label(7, i, vo.getPrice() == null ? ""
							: vo.getPrice() + "", normalFormat);
					ws.addCell(label7);
					Label label8 = new Label(8, i,
							vo.getQunaerPrice() == null ? "" : vo
									.getQunaerPrice() + "", normalFormat);
					ws.addCell(label8);
					Label label9 = new Label(9, i,
							vo.getQunaerSpreadAmount() == null ? "" : vo
									.getQunaerSpreadAmount() + "", normalFormat);
					ws.addCell(label9);
					Label label10 = new Label(10, i,
							vo.getQunaerSpread() == null ? "" : String.format("%.2f", vo
											.getQunaerSpread())+"%", normalFormat);
					ws.addCell(label10);
				}else{
					//填充空格
					Label label5 = new Label(5, i, "", normalFormat);
					ws.addCell(label5);
					Label label6 = new Label(6, i, "", normalFormat);
					ws.addCell(label6);
					Label label7 = new Label(7, i, "", normalFormat);
					ws.addCell(label7);
					Label label8 = new Label(8, i, "", normalFormat);
					ws.addCell(label8);
					Label label9 = new Label(9, i, "", normalFormat);
					ws.addCell(label9);
					Label label10 = new Label(10, i, "", normalFormat);
					ws.addCell(label10);
				}
				++i;
			}
			if (col < i) {
				ws.mergeCells(1, col, 1, i - 1);
				col = i;
			}
		}
		return ws;
	}

	private File createFile(String filePath, String fileName)
			throws IOException {
		String excelpath = filePath;
		System.out.println("输出路径：" + excelpath);
		// 创建文件
		File path = new File(excelpath);
		if (path.exists()) {
			path.delete();
			path.createNewFile();
		} else {
			path.mkdirs();
		}
		File file = new File(filePath + File.separator + fileName);
		file.createNewFile();
		return file;

	}

	private WritableSheet setHeadFirst(WritableSheet ws) throws Exception {
		this.formatWriteable();
		int row = 0;
		Label label0 = new Label(0, row, "序号", titleFormat);
		ws.addCell(label0);
		ws.setColumnView(0, COLUMN_WIDTH);
		Label label1 = new Label(1, row, "航线", titleFormat);
		ws.addCell(label1);
		ws.setColumnView(1, COLUMN_WIDTH);
		Label label2 = new Label(2, row, "日期", titleFormat);
		ws.addCell(label2);
		ws.setColumnView(2, COLUMN_WIDTH);
		Label label3 = new Label(3, row, "航班", titleFormat);
		ws.addCell(label3);
		ws.setColumnView(3, COLUMN_WIDTH);
		Label label4 = new Label(4, row, "航程类型", titleFormat);
		ws.addCell(label4);
		ws.setColumnView(4, COLUMN_WIDTH);
		Label label5 = new Label(5, row, "渠道", titleFormat);
		ws.addCell(label5);
		ws.setColumnView(5, COLUMN_WIDTH);
		Label label6 = new Label(6, row, "代理的平台名称", titleFormat);
		ws.addCell(label6);
		ws.setColumnView(6, COLUMN_WIDTH);
		Label label7 = new Label(7, row, "B2C价格", titleFormat);
		ws.addCell(label7);
		ws.setColumnView(7, COLUMN_WIDTH);
		Label label8 = new Label(8, row, "代理价格", titleFormat);
		ws.addCell(label8);
		ws.setColumnView(8, COLUMN_WIDTH);
		Label label9 = new Label(9, row, "价差金额", titleFormat);
		ws.addCell(label9);
		ws.setColumnView(9, COLUMN_WIDTH);
		Label label10 = new Label(10, row, "价差", titleFormat);
		ws.addCell(label10);
		ws.setColumnView(10, COLUMN_WIDTH);

		return ws;
	}

	private void formatWriteable() throws WriteException {
		titleFont = new jxl.write.WritableFont(WritableFont.createFont("宋体"),
				10, WritableFont.BOLD);
		titleFont.setColour(Colour.WHITE);
		titleFormat = new jxl.write.WritableCellFormat(titleFont);
		titleFormat.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
		titleFormat.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐
		titleFormat.setAlignment(Alignment.CENTRE); // 水平对齐
		titleFormat.setWrap(true); // 是否换行
		titleFormat.setBackground(Colour.OCEAN_BLUE);// 背景色暗灰-25%

		normalFont = new jxl.write.WritableFont(WritableFont.createFont("宋体"),
				11);
		normalFormat = new jxl.write.WritableCellFormat(normalFont);
		normalFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
		normalFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
		normalFormat.setAlignment(Alignment.CENTRE);
		normalFormat.setWrap(false);

		normalFont = new jxl.write.WritableFont(WritableFont.createFont("宋体"),
				11);

		boldFont = new jxl.write.WritableFont(WritableFont.createFont("宋体"),
				11, WritableFont.BOLD);
		boldFormat = new jxl.write.WritableCellFormat(boldFont);
		boldFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
		boldFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
		boldFormat.setAlignment(Alignment.CENTRE);
		boldFormat.setWrap(false);

	}

	/**
	 * 获得以当天开始连续10天的日期
	 * 
	 * @return
	 */
	private List<String> getDateList() {
		List<String> dateList = new ArrayList<String>();
		if (currentDate == null) {
			currentDate = DateUtils.getDateStr("yyyy-MM-dd");
		}
		dateList.add(currentDate);
		for (int i = 1; i <= COUNT - 1; i++) {
			dateList.add(DateUtils.getDateAfter(i));
		}

		return dateList;
	}

	public String getCurrentDate() {
		return currentDate;
	}

	public void setCurrentDate(String currentDate) {
		this.currentDate = currentDate;
	}

	public void setReportMap(Map<String, List<CsairReportVO>> reportMap) {
		this.reportMap = reportMap;
	}

	public void setDateList(List<String> dateList) {
		this.dateList = dateList;
	}

	@Override
	public Serializable getId() {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public boolean isNew() {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public boolean existed() {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public boolean notExisted() {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public boolean existed(String propertyName, Object propertyValue) {
		// TODO Auto-generated method stub
		return false;
	}

	public String getEmail() {
		return email;
	}

	public void setEmail(String email) {
		this.email = email;
	}
	public List<EmailVO> getEmailList() {
		return emailList;
	}

	public void setEmailList(List<EmailVO> emailList) {
		this.emailList = emailList;
	}

	class ComparatorReport implements Comparator {

		public int compare(Object arg0, Object arg1) {
			CsairReportVO user0 = (CsairReportVO) arg0;
			CsairReportVO user1 = (CsairReportVO) arg1;
			int flag = user0.getFromCity().compareTo(user1.getFromCity());
			if (flag == 0) {
				return user0.getToCity().compareTo(user1.getToCity());
			} else {
				return flag;
			}

			// 首先比较年龄，如果年龄相同，则比较名字

		}

	}
}
